package com.teradata.tool.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.teradata.tool.bean.EtlDailyReportInfoBean;
import com.teradata.tool.db.DbUtil;

public final class EtlDailyReportDao {

	private static final String	QUERY_ALL_ETL_DAILY_REPORT		= "SELECT * FROM "
																		+ DbUtil.getDbConfigurationInfo().getDbMpmtn()
																		+ ".ETL_Daily_Report WHERE TxDate=? ORDER BY ReportItem,ReportValue1;";

	private static final String	QUERY_ETL_DAILY_REPORTS_By_ITEM	= "SELECT * FROM "
																		+ DbUtil.getDbConfigurationInfo().getDbMpmtn()
																		+ ".ETL_Daily_Report WHERE TxDate>=? and TxDate<=? and ReportItem=? ORDER BY TxDate,";

	private static final String	QUERY_MAX_TXDATE				= "select max(txdate) from "
																		+ DbUtil.getDbConfigurationInfo().getDbMpmtn()
																		+ ".ETL_Daily_Report where txdate<>to_date('3000-12-31');";

	public static List<EtlDailyReportInfoBean> queryAllEtlDailyReport(Connection conn, Date txdate) throws SQLException {
		List<EtlDailyReportInfoBean> ret = new ArrayList<EtlDailyReportInfoBean>();
		PreparedStatement stat = conn.prepareStatement(QUERY_ALL_ETL_DAILY_REPORT);
		try {
			stat.setDate(1, txdate);
			ResultSet rs = stat.executeQuery();
			try {
				while (rs.next()) {
					EtlDailyReportInfoBean EtlDailyReportInfoBean = new EtlDailyReportInfoBean();
					int i = 1;
					EtlDailyReportInfoBean.setTxDate(rs.getDate(i++));
					EtlDailyReportInfoBean.setReportItem(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue1(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue2(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue3(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue4(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue5(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue6(rs.getString(i++));
					EtlDailyReportInfoBean.setMODIFY_DT(rs.getTime(i++));
					ret.add(EtlDailyReportInfoBean);
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return ret;
	}

	public static List<EtlDailyReportInfoBean> queryAllEtlDailyReportWithSort(Connection conn, Date txdate_start,
			Date txdate_end) throws SQLException {
		List<EtlDailyReportInfoBean> ret = new ArrayList<EtlDailyReportInfoBean>();
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "1", "ReportItem,ReportValue1;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "2", "ReportItem,ReportValue1;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "3", "ReportValue4 desc;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "4", "ReportValue5 desc;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "5", "ReportValue4 desc;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "6", "ReportItem,ReportValue1;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "7", "ReportItem,ReportValue1;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "8", "ReportItem,ReportValue1;"));
		addList(ret, queryEtlDailyReportsByItem(conn, txdate_start, txdate_end, "9", "ReportValue6 desc;"));
		return ret;
	}

	private static void addList(List<EtlDailyReportInfoBean> parentList, List<EtlDailyReportInfoBean> childList) {
		if (childList == null || childList.size() == 0)
			return;
		for (int i = 0; i < childList.size(); i++) {
			parentList.add(childList.get(i));
		}
	}

	public static List<EtlDailyReportInfoBean> queryEtlDailyReportsByItem(Connection conn, Date txdate_start,
			Date txdate_end, String reportItem, String orderBy) throws SQLException {
		List<EtlDailyReportInfoBean> ret = new ArrayList<EtlDailyReportInfoBean>();
		PreparedStatement stat = conn.prepareStatement(QUERY_ETL_DAILY_REPORTS_By_ITEM
				+ (orderBy == null ? ";" : orderBy));
		try {
			stat.setDate(1, txdate_start);
			stat.setDate(2, txdate_end);
			stat.setString(3, reportItem);
			ResultSet rs = stat.executeQuery();
			try {
				while (rs.next()) {
					EtlDailyReportInfoBean EtlDailyReportInfoBean = new EtlDailyReportInfoBean();
					int i = 1;
					EtlDailyReportInfoBean.setTxDate(rs.getDate(i++));
					EtlDailyReportInfoBean.setReportItem(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue1(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue2(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue3(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue4(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue5(rs.getString(i++));
					EtlDailyReportInfoBean.setReportValue6(rs.getString(i++));
					EtlDailyReportInfoBean.setMODIFY_DT(rs.getTime(i++));
					ret.add(EtlDailyReportInfoBean);
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return ret;
	}

	public static String getMaxTxdate(Connection conn) throws Exception {
		PreparedStatement stat = conn.prepareStatement(QUERY_MAX_TXDATE);
		try {
			ResultSet rs = stat.executeQuery();
			try {
				if (rs.next()) {
					return rs.getDate(1).toString();
				}
			} finally {
				rs.close();
			}
		} finally {
			stat.close();
		}
		return null;
	}
}
